package com.ksd.common.excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelReader {

    // 读取 Excel 文件并转换为 MovieExcel 列表
    public static List<MovieExcel> readExcelMovieFile(File excelFile) {
        List<MovieExcel> movieExcels = new ArrayList<>();
        // 用于存储表头列索引与属性名的映射
        Map<Integer, String> headerMap = new HashMap<>();

        try (InputStream inputStream = new FileInputStream(excelFile);
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表

            // 遍历行，先处理表头行
            boolean isHeaderRow = true;
            for (Row row : sheet) {
                if (isHeaderRow) {
                    // 处理表头，建立列索引到属性名的映射
                    for (Cell cell : row) {
                        String cellValue = getCellValue(cell);
                        // 根据表头内容确定对应属性名
                        if ("电影名称".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "title");
                        } else if ("电影类型".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "fileType");
                        } else if ("电影地址".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "videoAddress");
                        } else if ("封面地址".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "imgAddress");
                        }
                    }
                    isHeaderRow = false;
                } else {
                    // 处理数据行
                    MovieExcel movieExcel = new MovieExcel();
                    for (Cell cell : row) {
                        int columnIndex = cell.getColumnIndex();
                        String propertyName = headerMap.get(columnIndex);
                        if (propertyName != null) {
                            String cellValue = getCellValue(cell);
                            // 通过反射设置属性值
                            setFieldValue(movieExcel, propertyName, cellValue);
                        }
                    }
                    movieExcels.add(movieExcel);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return movieExcels;
    }

    // 获取单元格的值，根据单元格类型进行转换
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        String cellValue = "";
        switch (cell.getCellType()) {
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = cell.getDateCellValue().toString();
                } else {
                    cellValue = String.valueOf((long) cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }

    // 通过反射设置对象的属性值
    private static void setFieldValue(Object obj, String fieldName, String value) {
        try {
            Field field = obj.getClass().getDeclaredField(fieldName);
            field.setAccessible(true);
            field.set(obj, value);
        } catch (NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        File excelFile = new File("D:\\可视达\\播控后台管理系统_段江科\\硬盘数据资源\\电影1.xlsx");
        List<MovieExcel> movieExcels = readExcelMovieFile(excelFile);
        // 打印读取结果，验证是否正确赋值
//        for (MovieExcel movieExcel : movieExcels) {
//            System.out.println(movieExcel.toString());
//        }

        File musicFile = new File("D:\\可视达\\播控后台管理系统_段江科\\硬盘数据资源\\歌曲1.xlsx");
        List<MusicExcel> musicExcels = readExcelMusicFile(musicFile);
        for (MusicExcel musicExcel : musicExcels) {
            System.out.println(musicExcel.toString());
        }
    }

    // 读取 Excel 文件并转换为 MovieExcel 列表
    public static List<MusicExcel> readExcelMusicFile(File excelFile) {
        List<MusicExcel> musicExcels = new ArrayList<>();
        // 用于存储表头列索引与属性名的映射
        Map<Integer, String> headerMap = new HashMap<>();

        try (InputStream inputStream = new FileInputStream(excelFile);
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表

            // 遍历行，先处理表头行
            boolean isHeaderRow = true;
            for (Row row : sheet) {
                if (isHeaderRow) {
                    // 处理表头，建立列索引到属性名的映射
                    for (Cell cell : row) {
                        String cellValue = getCellValue(cell);
                        // 根据表头内容确定对应属性名
                        if ("歌曲名称".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "title");
                        } else if ("歌手".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "singer");
                        } else if ("音乐类型".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "fileType");
                        } else if ("专辑标题".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "albumTitle");
                        } else if ("专辑封面地址".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "logoAddress");
                        } else if ("歌曲地址".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "videoAddress");
                        } else if ("歌词地址".equals(cellValue)) {
                            headerMap.put(cell.getColumnIndex(), "lyricAddress");
                        }
                    }
                    isHeaderRow = false;
                } else {
                    // 处理数据行
                    MusicExcel musicExcel = new MusicExcel();
                    for (Cell cell : row) {
                        int columnIndex = cell.getColumnIndex();
                        String propertyName = headerMap.get(columnIndex);
                        if (propertyName != null) {
                            String cellValue = getCellValue(cell);
                            // 通过反射设置属性值
                            setFieldValue(musicExcel, propertyName, cellValue);
                        }
                    }
                    musicExcels.add(musicExcel);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return musicExcels;
    }
}

